

package com.dao.common;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.struts.action.ActionForm;

import com.dao.DataOperations;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.info.T_S_YHCZXXinfo;
import com.util.CommonTools;
import com.F03.actionform.F0306ActionForm;
import com.F03.dao.F0306DAO;
import com.config.Constants;

public class DbOperationExt extends DataOperations {

	Logger log = Logger.getLogger(DbOperationExt.class);

	public DbOperationExt() {
		super();
	}

	public DbOperationExt(Connection conn) {
		super(conn);
	}

	/**
	 * @操作记录语句
	 * */

	public StringBuffer getInsZzxxSQL(T_S_YHCZXXinfo info) {
		StringBuffer sql = null;
		String cznr = info.getF_VC_YHCZNR().trim();
		if ("insert".equals(cznr)) {
			cznr = "����";
		} else if ("update".equals(cznr)) {
			cznr = "�޸�";
		} else if ("delete".equals(cznr)) {
			cznr = "";
		} else {
			cznr = info.getF_VC_YHCZNR().trim();
		}
		try {
			sql = new StringBuffer();
			sql.append("INSERT INTO T_S_YHCZXX(F_VC_YHID,F_VC_YHMC,F_DT_YHCZSJ,F_VC_YHCZNR,F_VC_YHCZSM)");
			sql.append("VALUES('" + info.getF_VC_YHID() + "','" + info.getF_VC_YHMC() + "',");
			sql.append("getdate(),'" + cznr + "','" + info.getF_VC_YHCZSM() + "')");
			System.out.println("getInsZzxxSQL = " + sql.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sql;
	}

	/**
	 *@按列取數據
	 */
	public List queryData(String sql) throws SelectException {
		List list = new ArrayList();
		List collist = null;

		ResultSet rest = null;
		ResultSetMetaData resd = null;
		try {
			rest = super.executeQuery(sql);
			resd = rest.getMetaData();
			while (rest.next()) {
				collist = new ArrayList();
				for (int i = 1; i <= resd.getColumnCount(); i++) {
					collist.add(CommonTools.null2String(rest.getString(i)));
				}
				list.add(collist);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); /* �ر�rest��statement */
		}
		return list;
	}
	/**
	 *@按列取數據 加入总计

	 */
	public List queryDataTotal(String sql,String sqlTotal) throws SelectException {
		List list = new ArrayList();
		List collist = null;

		ResultSet rest = null;
		ResultSetMetaData resd = null;
		try {
			rest = super.executeQuery(sql);
			resd = rest.getMetaData();
			while (rest.next()) {
				collist = new ArrayList();
				for (int i = 1; i <= resd.getColumnCount(); i++) {
					collist.add(CommonTools.null2String(rest.getString(i)));
				}
				list.add(collist);
			}
			Dispose.dispose(rest);
			rest = super.executeQuery(sqlTotal);
			resd = rest.getMetaData();
			while(rest.next()){
				collist = new ArrayList();
				for (int i = 1; i <= resd.getColumnCount(); i++) {
					collist.add(CommonTools.null2String(rest.getString(i)));
				}
				list.add(collist);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); /* �ر�rest��statement */
		}
		return list;
	}

	
	/**
	 *读取动态表字段（用于生成导出EXCEL的SQL）
	 * @param String sql
	 * @return List
	 * @throws Exception
	 */
	public String ExcelSql(String tablename) throws SelectException {
		String sql = "";
		StringBuffer msql = new StringBuffer();
		StringBuffer sb = new StringBuffer();
		msql.append("SELECT * FROM " + tablename + " where ROWNUM = 1");
		ResultSet rest = null;
		ResultSetMetaData resd = null;
		try {
			rest = super.executeQuery(msql.toString());
			resd = rest.getMetaData();
			sb.append(" SELECT ");
			while (rest.next()) {
				for (int i = 1; i <= resd.getColumnCount(); i++) {
					sb.append(resd.getColumnLabel(i) + ",");
				}
			}
			sql = sb.substring(0, sb.length() - 1) + " from " + tablename + " where xh<>1";
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); /* �ر�rest��statement */
			this.disConnect();
		}
		return sql;
	}

	/**
	 *@执行存储过程(不含参数)
	 */
	public List queryDataCall(String sql, String str) throws SelectException {
		List list = new ArrayList();
		List collist = null;
		CallableStatement proc = null;
		ResultSet rest = null;
		ResultSetMetaData resd = null;
		try {
			proc = super.connection.prepareCall(" {call PRO_YLB()} ");
			proc.executeQuery();
			rest = super.executeQuery(sql);
			log.info("------------------------------");
			resd = rest.getMetaData();
			while (rest.next()) {
				collist = new ArrayList();
				for (int i = 1; i <= resd.getColumnCount(); i++) {
					collist.add(CommonTools.null2String(rest.getString(i)));
				}
				list.add(collist);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); /* �ر�rest��statement */
			this.disConnect();
		}
		return list;
	}

	/**
	 * 数据查询(存储过程包含参数)
	 * 
	 * @param String sql
	 * @return List
	 * @throws Exception
	 */
	public List queryDataCallWithParm(String sql, String str, String parameter) throws SelectException {
		List list = new ArrayList();
		List collist = null;
		CallableStatement proc = null;
		ResultSet rest = null;
		ResultSetMetaData resd = null;
		String value = "";
		try {
			proc = super.connection.prepareCall(str);
			if (!CommonTools.isNullString(parameter)) {
				String[] pars = parameter.split(",");
				for (int i = 0; i < pars.length; i++) {
					proc.setString(i + 1, pars[i]);
				}
			}
			proc.executeQuery();
			rest = super.executeQuery(sql);
			resd = rest.getMetaData();
			while (rest.next()) {
				collist = new ArrayList();
				for (int i = 1; i <= resd.getColumnCount(); i++) {
					value = CommonTools.null2String(rest.getString(i));
					if (!CommonTools.isNullString(parameter)) {
						if ("-1".equals(value)) {
							value = "��";
						}
						if ("0".equals(value)) {
							value = "";
						}
						if ("1".equals(value)) {
							value = "��";
						}
					}
					collist.add(value);
				}

				list.add(collist);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); /* �ر�rest��statement */
			this.disConnect();
		}
		return list;
	}

	/**
	 * @判断SQL结果集是否为空
	 * */
	public boolean JudgeDataExist(String sql) {
		boolean falg = true;
		ResultSet rest = null;
		try {
			log.info("sql is " + sql);
			rest = super.executeQuery(sql);
			if (rest.next()) {
				falg = false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return falg;
	}	 	
	
	
	/**
	 * 工艺类别模型
	 * @input productCode
	 * @return List
	 */
	public List List_t_critical_phases(String productCode) {

		List list = new ArrayList();
		Map map = null;

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT code,'('+serial+')'+phases phases FROM t_critical_phases ");
		sql.append("WHERE product_code='"+productCode+"' order by serial ");

		log.info(sql);

		ResultSet result = null;

		try {
			result = super.executeQuery(sql.toString());
			while (result.next()) {
				map = new HashMap();
				map.put("CODE", CommonTools.null2String(result.getString("code")));
				map.put("NAME", CommonTools.null2String(result.getString("phases")));
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(result);
		}
		return list;
	}
	
	/**
	 * @公用类下拉列表
	 * @String sql 
	 * @list
	 * @value=CODE
	 * @label=NAME
	 * */
	public List CODE_TABLE(String sql){
		log.info("CODE_TABLE is "+sql);
		List list = new ArrayList();
		Map map = null;
		ResultSet result = null;
		
//		加一个空白选项
		map = new HashMap();
		map.put("CODE", "");
		map.put("NAME", "");
		list.add(map);
		
		try {
			result = super.executeQuery(sql.toString());
			while (result.next()) {
				map = new HashMap();
				map.put("CODE", CommonTools.null2String(result.getString("CODE")));
				map.put("NAME", CommonTools.null2String(result.getString("NAME")));
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(result);
		}
		return list;
	}
	
	/**
	 * @码表查询(表名，查询的字段)
	 */

	public ArrayList queryTalbleList(String tableName, String[] columns, String where) throws SelectException{
		ArrayList list = new ArrayList();
		Map map = null;
		ResultSet rest = null;
		StringBuffer sql = new StringBuffer();		
		String column = "";
		String value = "";

		sql.append("select ").append(CommonTools.getArrayToString(columns, ",")).append(" ");
		sql.append("from ").append(tableName).append(" ");
		sql.append(where);
		log.info("queryTableList "+sql.toString());
		try{
			
			rest = super.executeQuery(sql.toString());
		
			while(rest.next()){
				map = new HashMap();
				// 取每一个字段和对应的数值
				for(int i = 0; i < columns.length; i++){
					column = CommonTools.trim(columns[i]);
					value = CommonTools.trim(rest.getString(column));
					map.put(column, value);
				}
				list.add(map);
			}
		}catch(SQLException e){
			throw new SelectException(e.getMessage());
		}finally{
			Dispose.dispose(rest); // 关闭rest及statement;
		}
		return list;
	}
	
	public List queryDataTotalOther(String sql,ActionForm actionForm,List totalList) throws SelectException {
		System.out.println("1111111111111111111111111111111111111111");
		List list = new ArrayList();
		List collist = null;
		ResultSet rest = null;
		ResultSetMetaData resd = null;
		try {
			rest = super.executeQuery(sql);
			System.out.println("sql-----"+sql);
			resd = rest.getMetaData();
			while (rest.next()) {
				collist = new ArrayList();
				for (int i = 1; i <= resd.getColumnCount(); i++) {
					collist.add(CommonTools.null2String(rest.getString(i)));
				}
				list.add(collist);
				System.out.println("size====="+list.size());
			}
			if(!totalList.isEmpty()){
				list.add(totalList);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); /* �ر�rest��statement */
		}
		return list;
	}
	
	/**
	 * @客户类别
	 * */
	public List List_c_customer_type(){
		List list = new ArrayList();
		list = CommonTools.chack_list(this.CODE_TABLE(Constants.c_customer_type));
		return list;
	}
	/**
	 * @地区分类
	 * */
	public List List_c_area_group(){
		List list = new ArrayList();
		list = CommonTools.chack_list(this.CODE_TABLE(Constants.c_area_group));
		return list;
	}
	/**
	 * @付款方式
	 * */
	public List List_c_payment_terms(){
		List list = new ArrayList();
		list = CommonTools.chack_list(this.CODE_TABLE(Constants.c_payment_terms));
		return list;
	}
	/**
	 * @销售员
	 * */
	public List List_c_saleman(){
		List list = new ArrayList();
		list = CommonTools.chack_list(this.CODE_TABLE(Constants.c_saleman));
		return list;
	}
	/**
	 * @材料家族表
	 * */
	public List List_pc_material_family(){
		List list = new ArrayList();
		list = CommonTools.chack_list(this.CODE_TABLE(Constants.pc_material_family));
		return list;
	}
	/**
	 * @材料列表
	 * */
	public List List_t_materials(){
		List list = new ArrayList();
		list = CommonTools.chack_list(this.CODE_TABLE(Constants.t_materials));
		return list;
	}
	/**
	 * @计量单位表
	 * */
	public List List_t_measure_units(){
		List list = new ArrayList();
		list = CommonTools.chack_list(this.CODE_TABLE(Constants.t_measure_units));
		return list;
	}
	/**
	 * @产品类别
	 * */
	public List List_t_product_type(){
		List list = new ArrayList();
		list = CommonTools.chack_list(this.CODE_TABLE(Constants.t_product_type));
		return list;
	}
	/**
	 * @模型类别
	 * */
	public List List_t_product_model(){
		List list = new ArrayList();
		list = CommonTools.chack_list(this.CODE_TABLE(Constants.t_product_model));
		return list;
	}
	/**
	 * @订单状态
	 * */
	public List List_state_type(){
		List list = new ArrayList();
		list = CommonTools.chack_list(this.CODE_TABLE(Constants.state_type));
		return list;
	}
	/**
	 * @库存原因

	 * */
	public List list_stock_reason_type(){
		List list = new ArrayList();
		list = CommonTools.chack_list(this.CODE_TABLE(Constants.c_stock_reason_type));
		return list;
	}
	/**
	 * @码表管理

	 * */
	public List list_t_tablename(){
		List list = new ArrayList();
		list = CommonTools.chack_list(this.CODE_TABLE(Constants.t_tablename));
		return list;
	}
	
//	动态加载数据
	public List filterList(String tableName,String code,String name,String strWhere){
		List list = new ArrayList();
		ResultSet rest = null;
		Map map = null;
		StringBuffer sql = new StringBuffer();
				
		sql.append("select distinct top 20 "+code+" code,"+name+" name from "+tableName+" where 1=1 ");
		if(!"".equals(strWhere)){
			sql.append(" and "+strWhere);
		}
//		sql.append(" order by newid(); ");
		log.info(sql.toString());
		try {
			log.info("filterList sql is "+sql);
			rest = super.executeQuery(sql.toString());
			while(rest.next()){
				map = new HashMap();
				map.put("name", CommonTools.null2String(rest.getString("name")));
				map.put("code", CommonTools.null2String(rest.getString("code")));
				list.add(map);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
//	动态加载数据

	public List filterListIn3(String tableName,String value1,String value2,String value3,String strWhere){
		List list = new ArrayList();
		ResultSet rest = null;
		Map map = null;
		StringBuffer sql = new StringBuffer();
				
		sql.append("select distinct top 20 "+value1+" value1,"+value2+" value2,"+value3+" value3 from "+tableName+" where 1=1 ");
		if(!"".equals(strWhere)){
			sql.append(" and "+strWhere);
		}
		try {
			rest = super.executeQuery(sql.toString());
			while(rest.next()){
				map = new HashMap();
				map.put("value1", CommonTools.null2String(rest.getString("value1")));
				map.put("value2", CommonTools.null2String(rest.getString("value2")));
				map.put("value3", CommonTools.null2String(rest.getString("value3")));
				list.add(map);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}	
}
